<?php
class Job_model extends CI_Model {

    function __construct()
    {
        parent::__construct();
    }
    
    function get($data)
    {
    	$this->db->select('job.*, job_category.name AS category, country.name AS country,
    	state.name AS state, job_hierarchy.name AS hierarchy, job_specialty.name AS specialty,
    	company.name AS company, course_level.name AS courselevel, course_type.name AS coursetype, drive_license.name As drivelicense');
    	$this->db->from('job');
    	$this->db->join('job_category', 'job.idcategory = job_category.id', 'left');
    	$this->db->join('job_hierarchy', 'job.idhierarchy = job_hierarchy.id', 'left');
    	$this->db->join('job_specialty', 'job.idspecialty = job_specialty.id', 'left');
    	$this->db->join('country', 'job.idcountry = country.id', 'left');
    	$this->db->join('state', 'job.idstate = state.id', 'left');
    	$this->db->join('company', 'job.idcompany = company.id', 'left');
    	$this->db->join('course_level', 'job.idcourselevel = course_level.id', 'left');
    	$this->db->join('course_type', 'job.idcoursetype = course_type.id', 'left');
    	$this->db->join('drive_license', 'job.iddrivelicense = drive_license.id', 'left');
    	
    	if (isset($data['idcompany'])) {
    		$this->db->where('job.idcompany', $data['idcompany']);
    	}
    	
    	if (isset($data['idjob']) && is_numeric($data['idjob'])) {
    		$this->db->where('job.id', $data['idjob']);
    	}
    	
    	if (isset($data['idlevel']) && is_numeric($data['idlevel'])) {
    		$this->db->where('job.idlevel', $data['idlevel']);
    	}
    	
    	if (isset($data['idhierarchy']) && is_numeric($data['idhierarchy'])) {
    		$this->db->where('job.idhierarchy', $data['idhierarchy']);
    	}
    	
    	if (isset($data['idcategory']) && is_numeric($data['idcategory'])) {
    		$this->db->where('job.idcategory', $data['idcategory']);
    	}
    	
    	if (isset($data['idspecialty']) && is_numeric($data['idspecialty'])) {
    		$this->db->where('job.idspecialty', $data['idspecialty']);
    	}
    	
    	if (isset($data['company']) && $data['company'] != '') {
    		$this->db->where('job.company', $data['company']);
    	}
    	
    	if (isset($data['startdate']) && $data['startdate'] != '') {
    		$this->db->where('job.startdate', $data['startdate']);
    	}
    	
   	 	if (isset($data['finishdate']) && $data['finishdate'] != '') {
    		$this->db->where('job.finishdate', $data['finishdate']);
    	}
    	
    	$this->db->order_by('job.id');
    	
    	return $this->db->get();
    }
    
	function get_job_keywords($data)
    {
    	$this->db->select('job_keyword.name');
    	$this->db->from('company_job_keyword');
    	$this->db->join('job_keyword', 'company_job_keyword.idkeyword = job_keyword.id', 'left');
    	
    	if (isset($data['idjob'])) {
    		$this->db->where('company_job_keyword.idjob', $data['idjob']);
    	}
    	
    	$this->db->order_by('job_keyword.name');
    	
    	return $this->db->get();
    }
    
	function insert($data)
    {
    	$this->db->trans_begin();
    	
    	$data_insert = array(
		   'title' => $data['title'],
		   'description' => $data['description'],
		   'idcategory' => $data['idcategory'],
		   'idcompany' => $data['idcompany'],
		   'idtravel' => $data['idtravel'],
		   'idflit' => $data['idflit'],
		   'idhomeoffice' => $data['idhomeoffice'],
		   'iddeficiency' => $data['iddeficiency'],
		   'idcoursecompleted' => $data['idcoursecompleted']
		);
		
		if (isset($data['idhierarchy']) && is_numeric($data['idhierarchy']))
		{
			$data_insert['idhierarchy'] = $data['idhierarchy'];
		}
		
    	if (isset($data['idspecialty']) && is_numeric($data['idspecialty']))
		{
			$data_insert['idspecialty'] = $data['idspecialty'];
		}
		
    	if (isset($data['idcountry']) && is_numeric($data['idcountry']))
		{
			$data_insert['idcountry'] = $data['idcountry'];
		}
		
    	if (isset($data['idstate']) && is_numeric($data['idstate']))
		{
			$data_insert['idstate'] = $data['idstate'];
		}
		
    	if (isset($data['idlevel']) && is_numeric($data['idlevel']))
		{
			$data_insert['idcourselevel'] = $data['idlevel'];
		}
		
    	if (isset($data['idcoursetype']) && is_numeric($data['idcoursetype']))
		{
			$data_insert['idcoursetype'] = $data['idcoursetype'];
		}
		
    	if (isset($data['minsalary']) && is_numeric($data['minsalary']))
		{
			$data_insert['minsalary'] = $data['minsalary'];
		}
		
    	if (isset($data['maxsalary']) && is_numeric($data['maxsalary']))
		{
			$data_insert['maxsalary'] = $data['maxsalary'];
		}
		
    	if (isset($data['iddrivelicense']) && is_numeric($data['iddrivelicense']))
		{
			$data_insert['iddrivelicense'] = $data['iddrivelicense'];
		}
		
		$this->db->set('date', 'NOW()', false);
		$this->db->insert('job', $data_insert);
		$data['idjob'] = $this->db->insert_id();
		
		$this->insert_job_keywords($data);
		
    	if ($this->db->trans_status() === false)
		{
		    $this->db->trans_rollback();
		    return false;
		}
		else
		{
		    $this->db->trans_commit();
		    return true;
		}
    }
    
    function insert_job_keywords($data)
    {
    	$this->db->where('idjob', $data['idjob']);
    	$this->db->delete('company_job_keyword');
    	
    	if ($data['keywords'])
    	{
	    	$array_keywords = explode(',', $data['keywords']);
			foreach ($array_keywords as $keyword)
			{
				$this->db->where('name', trim($keyword));
				$keyword_data = $this->db->get('job_keyword')->row();
				$id_keyword = $keyword_data ? $keyword_data->id : null;
				
				if (!is_numeric($id_keyword))
				{
					$this->db->insert('job_keyword', array('name' => trim($keyword)));
					$id_keyword = $this->db->insert_id();
				}
				
				$data_insert = array(
				   'idjob' => $data['idjob'],
				   'idkeyword' => $id_keyword
				);
				
				$this->db->insert('company_job_keyword', $data_insert);
			}
    	}
    }
    
	function update($data)
    {
    	$this->db->trans_begin();
    	
    	$data_insert = array(
		   'title' => $data['title'],
		   'description' => $data['description'],
		   'idcategory' => $data['idcategory'],
		   'idtravel' => $data['idtravel'],
		   'idflit' => $data['idflit'],
		   'idhomeoffice' => $data['idhomeoffice'],
		   'iddeficiency' => $data['iddeficiency'],
		   'idcoursecompleted' => $data['idcoursecompleted']
		);
		
		if (isset($data['idhierarchy']) && is_numeric($data['idhierarchy']))
		{
			$data_insert['idhierarchy'] = $data['idhierarchy'];
		}
		
    	if (isset($data['idspecialty']) && is_numeric($data['idspecialty']))
		{
			$data_insert['idspecialty'] = $data['idspecialty'];
		}
		
    	if (isset($data['idcountry']) && is_numeric($data['idcountry']))
		{
			$data_insert['idcountry'] = $data['idcountry'];
		}
		
    	if (isset($data['idstate']) && is_numeric($data['idstate']))
		{
			$data_insert['idstate'] = $data['idstate'];
		}
		
    	if (isset($data['idlevel']) && is_numeric($data['idlevel']))
		{
			$data_insert['idcourselevel'] = $data['idlevel'];
		}
		
    	if (isset($data['idcoursetype']) && is_numeric($data['idcoursetype']))
		{
			$data_insert['idcoursetype'] = $data['idcoursetype'];
		}
		
    	if (isset($data['minsalary']) && is_numeric($data['minsalary']))
		{
			$data_insert['minsalary'] = $data['minsalary'];
		}
		
    	if (isset($data['maxsalary']) && is_numeric($data['maxsalary']))
		{
			$data_insert['maxsalary'] = $data['maxsalary'];
		}
		
    	if (isset($data['iddrivelicense']) && is_numeric($data['iddrivelicense']))
		{
			$data_insert['iddrivelicense'] = $data['iddrivelicense'];
		}
		
		$this->db->where('id', $data['idjob']);
		$this->db->update('job', $data_insert);
		
		$this->insert_job_keywords($data);
		
    	if ($this->db->trans_status() === false)
		{
		    $this->db->trans_rollback();
		    return false;
		}
		else
		{
		    $this->db->trans_commit();
		    return true;
		}
    }
    
	function delete($data)
    {
    	$this->db->trans_begin();
		
		$this->db->delete('company_job_keyword', array('idjob' => $data['id']));
		$this->db->delete('job', array('id' => $data['id']));
		
    	if ($this->db->trans_status() === false)
		{
		    $this->db->trans_rollback();
		    return false;
		}
		else
		{
		    $this->db->trans_commit();
		    return true;
		}
    }
    
 	function search($data)
    {
    	$this->db->select('job.id, job.date, company.idmember AS idmember');
    	$this->db->from('job');
    	$this->db->join('company', 'job.idcompany = company.id', 'left');
    	
    	if (isset($data['keyword'])) {
    		$this->db->like('job.title', $data['keyword']);
    		$this->db->or_like('company.name', $data['keyword']);
    	}
    	
    	$this->db->order_by('job.id desc');
    	
    	if (!isset($data['limit']) || !is_numeric($data['limit']))
    	{
    		$data['limit'] = 15;
    	}
    	
    	if (!isset($data['offset']) || !is_numeric($data['offset']))
    	{
    		$data['offset'] = 0;
    	}
    	elseif ($data['offset'] > 0)
    	{
    		$data['offset'] = ($data['limit'] * $data['offset']);
    	}
    	
    	$this->db->limit($data['limit'], $data['offset']);
    	
    	return $this->db->get();
    }
    
	function get_count_jobs($data)
    {
    	$this->db->select('job.id, job.date, company.idmember AS idmember');
    	$this->db->from('job');
    	$this->db->join('company', 'job.idcompany = company.id', 'left');
    	
    	if (isset($data['keyword'])) {
    		$this->db->like('job.title', $data['keyword']);
    		$this->db->or_like('company.name', $data['keyword']);
    	}
    	return $this->db->count_all_results();
    }
}
?>